﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using AMS.DataAccess.Common;
using AMS.DataAccess.Trademark.Interfaces;
using AMS.DataAccess.Trademark.Filters;
using AMS.DataAccess.Sql;
using AMS.DataAccess.Trademark.BO;

namespace AMS.DataAccess.Trademark
{
    public class RecvFileManager : IRecvFileManager
    {
        /// <summary>
        /// Get RF category
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllCategoryRecvFiles()
        {
            string sql = "SELECT * FROM TD_TMRFCategory ORDER BY Source, RFName";
            var ds = SqlDBHelper.Query(sql);
            return ds.Tables[0];
        }


        public List<RecvFileInfo> GetPagedRecvFiles(CallContext context, RecvFileFilter filter, int pageIndex, int pageSize)
        {
            int total;
            return GetPagedRecvFiles(context, filter, pageIndex, pageSize, false, out total);
        }

        public List<RecvFileInfo> GetPagedRecvFiles(CallContext context, RecvFileFilter filter, int pageIndex, int pageSize, bool caculateTotal, out int totalCount)
        {
            totalCount = 0;
            SqlBuilder sql = new SqlBuilder();
            sql.Add("SELECT rf.*");
            sql.Add("    ,c.CaseIndex");
            sql.Add("    ,cus.Name AS CustomerName");
            sql.Add("    ,o.OfficeName");
            sql.Add("FROM TT_RecvFile rf");
            sql.Add("LEFT JOIN VT_Case c ON rf.CaseID = c.AID");
            sql.Add("LEFT JOIN TC_Customer cus ON rf.CustomerID = cus.AID");
            sql.Add("LEFT JOIN TD_TMOffice o ON rf.OfficeID = o.OfficeID");
            ApplyFilter(sql, filter);

            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            SqlInfo sqlInfo = new SqlInfo();
            sqlInfo.BaseSql = sql.ToString();
            sqlInfo.PKField = "AID";

            PagedSqlInfo pagedSql = new PagedSqlInfo();
            PagerWrapper.WrapSql(sqlInfo, new PagerInfo(pageIndex, pageSize), ref pagedSql);

            var ds = SqlDBHelper.Query(pagedSql.PagedSql);
            var list = BusinessObjectMapping.DataTableToBOList<RecvFileInfo>(ds.Tables[0]);
            if (caculateTotal)
            {
                var totalDS = SqlDBHelper.Query(pagedSql.TotalCountSql);
                totalCount = AdoConverter.ToInt32(totalDS.Tables[0].Rows[0][0]);
            }

            return list;
        }

        void ApplyFilter(SqlBuilder sql, RecvFileFilter filter)
        {
            if (filter == null)
            {
                return;
            }

            sql.Add("WHERE 1=1");
            if (filter.Owner != 0)
            {
                sql.Add("    AND rf.CreateUserID = ", filter.Owner.ToString());
            }
            if (filter.CaseIndex != null)
            {
                sql.Add("    AND c.CaseIndex", filter.CaseIndex.ToString());
            }
            if (filter.RecvFileName != null)
            {
                sql.Add("    AND rf.RFName", filter.RecvFileName.ToString());
            }
            if (filter.CreateDate != null)
            {
                sql.Add("    AND ", string.Format(filter.CreateDate.ToString(), "rf.CreateDateTime"));
            }
            if (filter.RecvFileTypes != null && filter.RecvFileTypes.Count > 0)
            {
                SqlConditionList condlist = new SqlConditionList("rf.Source", filter.RecvFileTypes);
                sql.Add("    AND ", condlist.ToString());
            }
        }

        public void AddRecvFile(CallContext context, RecvFileInfo rf)
        {
            // TODO: enable create user id
            //if (rf.CreateUserID == 0 && context != null)
            //{
            //    rf.CreateUserID = context.UserID;
            //}
            SqlInsert sql = new SqlInsert("TT_RecvFile");
            sql.AddBusinessObject(rf);
            sql.Add("SELECT @@IDENTITY");

            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            var ds = SqlDBHelper.Query(sql.ToString());
            rf.AID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        public List<RFTaskInfo> GetRFTask(CallContext context, RFTaskFilter filter)
        {
            SqlBuilder sql = new SqlBuilder();
            sql.Add("SELECT * FROM VTD_RFTask");
            sql.Add("WHERE 1=1");
            if (filter != null)
            {
                if (filter.RFIndex != null)
                {
                    sql.Add("    AND RFIndex", filter.RFIndex.ToString());
                }
                if (filter.CustomerID != null && filter.CustomerID.Value != 0)
                {
                    SqlConditionGroup group = new SqlConditionGroup(LogicOperations.OR);
                    group.Add(new SqlCondition("CustomerID", 0));
                    group.Add(new SqlCondition("CustomerID", filter.CustomerID.Value));
                    sql.Add("    AND", group.ToString());
                }
                else
                {
                    sql.Add("    AND CustomerID = 0");
                }
                if (filter.RecvFileID != null)
                {
                    SqlBuilder sqlSelect = new SqlBuilder();
                    sqlSelect.Add("SELECT TOP 1 RFIndex");
                    sqlSelect.Add("FROM TT_RecvFile");
                    sqlSelect.Add("WHERE AID = ", filter.RecvFileID.Value.ToString());

                    sql.Add("    AND RFIndex =(");
                    sql.Add(sqlSelect.ToString(), ")");
                }
            }

            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            var ds = SqlDBHelper.Query(sql.ToString());
            var list = BusinessObjectMapping.DataTableToBOList<RFTaskInfo>(ds.Tables[0]);
            return list;
        }

        public bool SetRFAlreadyGenerateTaskFlag(CallContext context, int rfId)
        {
            SqlUpdate update = new SqlUpdate("TT_RecvFile");
            update.AddColumn("isGenTask", 1);
            update.AddCondition(new SqlCondition("AID", rfId));

            AMS.DataAccess.SqlDebugger.WriteLine(update);

            return 0 < SqlDBHelper.ExecuteSql(update.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public List<RecvFileInfo> GetNewRecvFiles(CallContext context)
        {
            SqlBuilder sql = new SqlBuilder();
            sql.Add("select r.AID");
            sql.Add("    ,r.RFName");
            sql.Add("    ,c.AID AS CaseID");
            sql.Add("    ,c.CaseIndex");
            sql.Add("from TT_RecvFile r");
            sql.Add("LEFT JOIN VT_Case c ON r.CaseID = c.AID");
            sql.Add("where isGenTask = 0");
            sql.Add("ORDER BY r.AID DESC");

            var ds = SqlDBHelper.Query(sql.ToString());
            var list = BusinessObjectMapping.DataTableToBOList<RecvFileInfo>(ds.Tables[0]);
            return list;
        }

        public RecvFileInfo GetRecvFile(CallContext context, int rfId)
        {
            SqlBuilder sql = new SqlBuilder();
            sql.Add("SELECT rf.*");
            sql.Add("    ,c.CaseIndex");
            sql.Add("    ,cus.Name AS CustomerName");
            sql.Add("    ,o.OfficeName");
            sql.Add("FROM TT_RecvFile rf");
            sql.Add("LEFT JOIN VT_Case c ON rf.CaseID = c.AID");
            sql.Add("LEFT JOIN TC_Customer cus ON rf.CustomerID = cus.AID");
            sql.Add("LEFT JOIN TD_TMOffice o ON rf.OfficeID = o.OfficeID");
            sql.Add("WHERE rf.AID = ", rfId.ToString());

            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            var ds = SqlDBHelper.Query(sql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return BusinessObjectMapping.DataRowToBO<RecvFileInfo>(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public bool UpdateRecvFile(CallContext context, RecvFileInfo rf)
        {
            SqlUpdate update = new SqlUpdate("TT_RecvFile");
            update.AddBusinessObject(rf);
            update.AddCondition(new SqlCondition("AID", rf.AID));

            AMS.DataAccess.SqlDebugger.WriteLine(update);

            return 0 < SqlDBHelper.ExecuteSql(update.ToString());
        }
    }
}
